package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.ChanPin;
import com.ysd.entity.ChanPinXiangQing;
import com.ysd.util.DBManger;

public class ChanPinXiangQingDaoImpl {
	public List<ChanPinXiangQing> getChanPinXiangQing(int cpxqId) {// 什么时候有参数呢？看sql语句有没有条件；
		List<ChanPinXiangQing> cpxqList = new ArrayList<ChanPinXiangQing>();
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from shangpinxiangqing where cpWaiJianId=" + cpxqId);
			while (rs.next()) {
				String xqId = rs.getString("chanPinId");
				String xqName = rs.getString("chanPinName");
				String xqGongNeng = rs.getString("chanPinGongNeng");
				String xqShouJia = rs.getString("chanPinShouJia");
				String xqJia = rs.getString("shiChangJia");
				String xqPinPai = rs.getString("shangPinPinPai");
				int xqwjId = rs.getInt("cpWaiJianId");
				String xqLoGo = rs.getString("chanPinXiangQingLoGo");
				ChanPinXiangQing cpXiangQing = new ChanPinXiangQing(xqId, xqName, xqGongNeng, xqShouJia, xqJia,
						xqPinPai, xqwjId, xqLoGo);
				cpxqList.add(cpXiangQing);
			}
			return cpxqList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManger.closeAll(conn, st, rs);
		}
	}

	public List<ChanPinXiangQing> getChanPinXiangQing(String chaxunName) {
		List<ChanPinXiangQing> chaxunList = new ArrayList<ChanPinXiangQing>();
		Connection conn=DBManger.getConnection();
		Statement st = null;
		ResultSet rs=null;
		
		try {
			st=conn.createStatement();
			rs=st.executeQuery("select * FROM shangpinxiangqing WHERE chanPinName LIKE'%"+chaxunName+"%'");
			while(rs.next()) {
				String chanpinid=rs.getString("chanPinId");
				String chanpinname=rs.getString("chanPinName");
				String chanpinlogo=rs.getString("chanPinXiangQingLoGo");
				ChanPinXiangQing chanpinchaxun = new ChanPinXiangQing(chanpinid, chanpinname, chanpinlogo);
				chaxunList.add(chanpinchaxun);
			}
			return chaxunList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
		
	}
	
	/**
	 * 多条件查询产品详情
	 * 返回List 查询时可能时多条数据
	 */
	public List<ChanPinXiangQing> getProductsByWhere(ChanPinXiangQing cpxq){
		List<ChanPinXiangQing> chaxun1List = new ArrayList<ChanPinXiangQing>();
		
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			String sql = "select sp.*,cp.chanPinXiLie from shangpinxiangqing sp,chanpinxilie cp  where 1=1";//where 1=1为永真 目的是为了解决拼接where条件
			if (cpxq.getChanPinName()!=null && !cpxq.getChanPinName().equals("")) {
				sql += " and chanPinName like '%"+cpxq.getChanPinName()+"%'";
			}
			
			if (cpxq.getChanPinGongNeng()!=null&&!cpxq.getChanPinGongNeng().equals("")) {
				sql +=" and chanPinGongNeng like '%"+cpxq.getChanPinGongNeng()+"%'";
			}
			
			if(cpxq.getShangPinPinPai()!=null&&!cpxq.getShangPinPinPai().equals("")) {
				sql +=" and shangPinPinPai like '%"+cpxq.getShangPinPinPai()+"%'";
			}
			
			if (cpxq.getBeginProductPrice()>0) {
				sql+=" and shiChangJia > "+cpxq.getBeginProductPrice();
			}
			
			if (cpxq.getEndProductPrice()>0) {
				sql +=" and shiChangJia < "+cpxq.getEndProductPrice();
			}
			
			if (cpxq.getBeginProductPrice1()>0) {
				sql+=" and chanPinShouJia > "+cpxq.getBeginProductPrice1();
			}
			
			if (cpxq.getEndProductPrice1()>0) {
				sql +=" and chanPinShouJia < "+cpxq.getEndProductPrice1();
			}
			
			if (cpxq.getXiangQingWaiJianId()!=0) {
				sql +=" and xiangQingWaiJianId ="+cpxq.getXiangQingWaiJianId();
			}
			
			sql +=" and sp.xiangQingWaiJianId = cp.waiJianId";
			System.out.println(sql);//打印拼好的sql语句，方便以后在控制台观看sql语句是否有误
			
			rs = st.executeQuery(sql);
			while(rs.next()) {
				String chanPinId = rs.getString("chanPinId");
				String chanPinName = rs.getString("chanPinName");
				String chanPinGongNeng = rs.getString("chanPinGongNeng");

				String chanPinShouJia = rs.getString("chanPinShouJia");
				String shiChangJia = rs.getString("shiChangJia");
				String shangPinPinPai = rs.getString("shangPinPinPai");

				String chanPinXiangQingLoGo =rs.getString("chanPinXiangQingLoGo");
				String chanPinXiLie =rs.getString("chanPinXiLie");
				int xiangQingWaiJianId =rs.getInt("xiangQingWaiJianId");

				
				ChanPinXiangQing ChanPinXiangQing = new ChanPinXiangQing(chanPinId, chanPinName, chanPinGongNeng, chanPinShouJia, shiChangJia, shangPinPinPai, chanPinXiangQingLoGo, chanPinXiLie, xiangQingWaiJianId);
				chaxun1List.add(ChanPinXiangQing);
				
				
			}
			return chaxun1List;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 
	 * 添加产品的方法
	 * 返回受影响行数
	 */ 
	public int addProduct(ChanPinXiangQing addCp) {
		return DBManger.executeUpdate("INSERT into shangpinxiangqing values(null,'"+addCp.getChanPinName()+"','"+addCp.getChanPinGongNeng()+"','"+addCp.getChanPinShouJia()+"',null,'"+addCp.getShangPinPinPai()+"',null,'"+addCp.getChanPinXiangQingLoGo()+"',"+addCp.getXiangQingWaiJianId()+")");
		
	}
	/**
	 * 通过产品Name查询产品
	 * @param name
	 * @return
	 */
	public List<ChanPinXiangQing> getChanPins(String names){
		List<ChanPinXiangQing> xqList = new ArrayList<ChanPinXiangQing>();
		
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs=st.executeQuery("SELECT * from shangpinxiangqing where chanPinName='"+names+"'");
			while (rs.next()) {
				String chanPinId = rs.getString("chanPinId");
				String chanPinName = rs.getString("chanPinName");
				String chanPinGongNeng = rs.getString("chanPinGongNeng");
				String chanPinShouJia = rs.getString("chanPinShouJia");
				String shiChangJia = rs.getString("shiChangJia");
				String shangPinPinPai = rs.getString("shangPinPinPai");

				String chanPinXiangQingLoGo =rs.getString("chanPinXiangQingLoGo");

				int xiangQingWaiJianId =rs.getInt("xiangQingWaiJianId");
				ChanPinXiangQing cp = new ChanPinXiangQing(chanPinId, chanPinName, chanPinGongNeng, chanPinShouJia, shiChangJia, shangPinPinPai, chanPinXiangQingLoGo, "", xiangQingWaiJianId);
				xqList.add(cp);
				
			}
			return xqList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 删除产品的方法
	 */
	
	public int deleteProductById(int cId) {
		return DBManger.executeUpdate("DELETE from shangpinxiangqing where chanPinId = "+cId+"");
	}
	
	/**
	 * 回显数据Id 
	 */
	
	public ChanPinXiangQing getCPById(int Id) {
		Connection conn = DBManger.getConnection();
		Statement st = null;
		ResultSet rs = null;
		ChanPinXiangQing ChanPinXiangQing=null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from shangpinxiangqing where  chanPinId = "+Id);
			while(rs.next()) {
				String chanPinId = rs.getString("chanPinId");
				String chanPinName = rs.getString("chanPinName");
				String chanPinGongNeng = rs.getString("chanPinGongNeng");
				String chanPinShouJia = rs.getString("chanPinShouJia");
				String shangPinPinPai = rs.getString("shangPinPinPai");
				String chanPinXiangQingLoGo =rs.getString("chanPinXiangQingLoGo");
				 ChanPinXiangQing = new ChanPinXiangQing(chanPinId, chanPinName, chanPinGongNeng, chanPinShouJia, "", shangPinPinPai, chanPinXiangQingLoGo, "", 0);
			}
			return ChanPinXiangQing;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManger.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 产品修改
	 * 返回受影响行数
	 * 
	 */
	public int updateCP(ChanPinXiangQing cp) {
		String sql = "update shangpinxiangqing set chanPinName = '"+cp.getChanPinName()+"' ,chanPinGongNeng='"+cp.getChanPinGongNeng()+"' ,chanPinShouJia ='"+cp.getChanPinShouJia()+"' ,shangPinPinPai='"+cp.getShangPinPinPai()+"',chanPinXiangQingLoGo='"+cp.getChanPinXiangQingLoGo()+"' where chanPinId = "+cp.getChanPinId();
		System.out.println(sql);
		return DBManger.executeUpdate(sql);
	}
}


























